package bdteste;

import basededados.Contact;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DB {

    private String Server = "";
    private String DataBase = "";
    private String Username = "";
    private String Password = "";
    private Connection Conn = null;
    private Statement Stmt = null;

    public DB(String server, String database, String user, String pass) {
        this.Server = server;
        this.DataBase = database;
        this.Username = user;
        this.Password = pass;
        loadDriver();
        openConn();
    }

    // Metodo que tenta abrir uma conexão à base de dados, caso não seja possível a ligação lança a excepção
    private void openConn() {
        try {
            String url = "jdbc:mysql://" + this.Server + "/" + this.DataBase + "?" + "user=" + this.Username + "&password=" + this.Password;
            Conn = (Connection) DriverManager.getConnection(url);
        } catch(com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException ex){
            System.out.println("Connection Error!\nReason: Unknown DataBase '"+DataBase+"'");
            Conn = null;
        } catch (com.mysql.jdbc.exceptions.jdbc4.CommunicationsException ex) {
            System.out.println("Connection Error!\nReason: Unknown Server '"+Server+"'");
            Conn = null;
        }catch (SQLException ex) {
            System.out.println("Connection Error!\nReason: Access Denied for user '"+this.Username+"@"+this.Server+"'");
            Conn = null;
        }
    }

    public void closeCon() {
        if(exist()){
            try {
                Conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
            }
        }else{
            System.out.println("Connection Refused! [Method 'closeCon()']");
        }
    }

    private void openStmt() {
        if(exist()){
            try {
                Stmt = (Statement) Conn.createStatement();
            } catch (SQLException ex) {
                Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
            }
        }else{
            System.out.println("Connection Refused! [Method 'openStmt()']");
        }
    }

    private void closeStmt() {
        if(exist()){
            try {
                Stmt.close();
            } catch (SQLException ex) {
                Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
            }
        }else{
            System.out.println("Connection Refused! [Method 'closeStmt()']");
        }
    }

    public void showTable(String table) {
        if(exist()){
            openStmt();
            if (table.equals("contacts")) {
                int n = 5;
                try {
                    ResultSet rs = Stmt.executeQuery("SELECT * FROM "+ table);

                    while (rs.next()) {
                        System.out.println("*********************************************************");

                        for (int i=1; i<=n; i++){
                            if(i==1){
                                System.out.print("* "+rs.getString(i));
                            }else if(i==n){
                                System.out.print(" * "+rs.getString(i)+" *\n");
                            }else{
                                System.out.print(" * "+rs.getString(i));
                            }
                        }
                        if(rs.isLast()){
                            System.out.println("*********************************************************");
                        }
                    }

                } catch (SQLException ex) {
                    Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
                }
            } else {
                System.out.println("Unknown Table '"+table+"'");
            }
            closeStmt();
        }else{
            System.out.println("Connection Refused! [Method 'showTable()']");
        }
    }

    public void updateEmail(String name, String email) {
        if(exist()){
            String SQL = "UPDATE contacts SET email='" + email + "' WHERE name='" + name + "'";
            executeSQL(SQL);
        }else{
            System.out.println("Connection Refused! [Method 'updateEmail()']");
        }
    }

    private void loadDriver() {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void executeSQL(String SQL) {
        if(exist()){
            openStmt();
            try {
                Stmt.executeUpdate(SQL);
            } catch (SQLException ex) {
                System.out.println("SQL ERROR!\n"
                        + "SQL Line: '" + SQL + "'\n"
                        + "Reason: " + ex.getMessage());
            }
            closeStmt();
        }else{
            System.out.println("Connection Refused! [Method 'executeSQL()']");
        }
    }

    public void add(Contact c, String table) {
        if(exist()){
            if(table.equals("contacts")){
                String SQL = "INSERT INTO "+table+" VALUES ('0', '"+c.getName()+"', '"+c.getNumber()+"','"+c.getGrade()+"','"+c.getEmail()+"')";
                executeSQL(SQL);
            }else{
                System.out.println("Unknown Table '"+table+"'");
            }
        }else{
            System.out.println("Connection Refused! [Method 'add()']");
        }
    }

    public void remove(int i, String table) {
        if(exist()){
            if(table.equals("contacts")){
                executeSQL("DELETE FROM "+table+" WHERE id="+i);
            }else{
                System.out.println("Unknown Table '"+table+"'");
            }
        }else{
            System.out.println("Connection Refused! [Method 'remove()']");
        }
    }

    private boolean exist(){
        return (Conn != null);
    }
}